package com.hei.dao.daoImpl;

import com.fasterxml.jackson.databind.BeanProperty;
import com.hei.dao.RoleDao;
import com.hei.pojo.Role;
import com.hei.pojo.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@Repository
public class RoleDaoImpl implements RoleDao {

    private List<Role> roles;
    private ComboPooledDataSource dataSource;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Role> findAll() {
        String sql = "select * from role";
        List<Role> roles = new ArrayList<Role>();
        roles =  jdbcTemplate.query("select * from sys_role", new BeanPropertyRowMapper<Role>(Role.class));
        return roles;
    }
    @Override
    public int addRole(Role role){
        String sql = "INSERT INTO sys_role (roleName, roleDesc) VALUES (?, ?)";
        return jdbcTemplate.update(sql,role.getRoleName(),role.getRoleDesc());
    }

    @Override
    public List<User> showUserList() {
        List<User> users = new ArrayList<User>();
        users =  jdbcTemplate.query("select * from sys_user", new BeanPropertyRowMapper<User>(User.class));
        return users;
    }

    public int addUser(User user){
        String sql = "INSERT INTO sys_user (userName, email,password,phoneNum) VALUES (?, ?, ?, ?)";
        //捕获主键
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getEmail());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getPhoneNum());
            return ps;
        },keyHolder);

        int id = keyHolder.getKey().intValue();

        if(user.getJob()!=null){
            int[] jobs = user.getJob();
            for (int i = 0; i < jobs.length; i++) {
                System.out.println("userId:"+user.getId()+"job:"+jobs[i]);
                String sql2 = "INSERT INTO sys_user_role (userId,roleId) VALUES (?, ?)";
                jdbcTemplate.update(sql2,id,jobs[i]);
            }
        }

        return id;
    }

    @Override
    public int delUser(int id) {
        String sql = "delete from sys_user where id = ?";
        return jdbcTemplate.update(sql,id);
    }

    @Override
    public String getJobName(int id) {
        String sql = "select roleName from sys_role where id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, String.class);
        }catch (Exception e){
            return ("未知职位");
        }
    }

    @Override
    public int[] getJobs(int id) {
        String sql = "select roleId from sys_user_role where userId = ?";
        List<Integer> job = jdbcTemplate.queryForList(sql,Integer.class,id);
        return job.stream().mapToInt(i->i).toArray();
    }

    @Override
    public User getUser(int id) {
        String sql = "select * from sys_user where id = ?";
        return (User) jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class),id);
    }

    @Override
    public int updateUser(User user){
        String sql = "update sys_user set username = ?,  email = ?, password = ?, phoneNum = ?,job = ? where id = ?";
        return  jdbcTemplate.update(sql,user.getUsername(),user.getEmail(),user.getPassword(),user.getPhoneNum(),user.getJob(),user.getId());
    }

    @Override
    public int delRole(int id) {
        String sql1 = "delete from sys_user_role where roleId = ?";
        String sql2 = "delete from sys_role where id = ?";
        jdbcTemplate.update(sql1,id);
        return jdbcTemplate.update(sql2,id);
    }

    @Override
    public int updateRole(Role role) {
        String sql = "update sys_role set roleName = ?, roleDesc = ? where id = ?";
        int i = jdbcTemplate.update(sql,role.getRoleName(),role.getRoleDesc(),role.getId());
        System.out.println(role.getId()+role.getRoleName()+role.getRoleDesc());
        return i;
    }

    @Override
    public Role getRole(int id) {
        String sql = "select * from sys_role where id = ?";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Role.class),id);
    }

    @Override
    public boolean login(String username, String password) {
        String sql = "select * from sys_user where username = ? and password = ?";
        List<User> users = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),username,password);
        if (!users.isEmpty()){
            System.out.println("success");
            return true;
        }else {
            System.out.println("fail");
            return false;
        }

    }
}

